package db;

import java.sql.SQLException;
import java.sql.Statement;

/**
 * Class om tables aan te maken
 *
 */

public class BuildDB
{

	private static String buildGebruikerTable = "CREATE TABLE  gebruikers (g_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY ,gnaam VARCHAR( 40 ) NOT NULL ,adres VARCHAR( 255 ) NOT NULL ,email VARCHAR( 255 ) NOT NULL, wachtwoord VARCHAR( 255 ) NOT NULL ,geboortedatum VARCHAR(255) NOT NULL ,UNIQUE (email))";
	private static String buildVeilingTable = "CREATE TABLE  veilingen    (v_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY ,g_id INT NOT NULL ,begintijd TIMESTAMP  NOT NULL ,eindtijd TIMESTAMP  NOT NULL ,titel VARCHAR( 255 ) NOT NULL ,beschrijving VARCHAR(2048) NOT NULL ,minimumbod INT,FOREIGN KEY (g_id) REFERENCES Gebruikers(g_id))";
	private static String buildBiedingTable = "CREATE TABLE  biedingen    (b_id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY ,v_id INT NOT NULL ,g_id INT NOT NULL ,biedtijdstip TIMESTAMP  NOT NULL ,bod INT NOT NULL,FOREIGN KEY (g_id) REFERENCES Gebruikers(g_id),FOREIGN KEY (v_id) REFERENCES Veilingen(v_id))";

	public static void buildTables()
	{
		Statement stmt;
		try
		{
			stmt = DBManager.getInstance().getConnection().createStatement();
			try
			{
				stmt.execute("SELECT * FROM gebruikers");
			} catch (SQLException e)
			{
				stmt.execute(buildGebruikerTable);
				System.out.println("Gebruiker table aangemaakt");
			}

			try
			{
				stmt.execute("SELECT * FROM veilingen");
			} catch (SQLException e)
			{
				stmt.execute(buildVeilingTable);
				System.out.println("Veilingen table aangemaakt");
			}

			try
			{
				stmt.execute("SELECT * FROM biedingen");
			} catch (SQLException e)
			{
				stmt.execute(buildBiedingTable);
				System.out.println("Biedingen table aangemaakt");
			}
		} catch (SQLException e1)
		{
			e1.printStackTrace();
		}
	}

	public static void dropTables()
	{
		Statement stmt;
		try
		{
			stmt = DBManager.getInstance().getConnection().createStatement();

			try
			{
				stmt.execute("DROP TABLE biedingen");
			} catch (SQLException e)
			{
				System.out.println("no table biedingen");
			}

			try
			{
				stmt.execute("DROP TABLE veilingen");
			} catch (SQLException e)
			{
				System.out.println("no table veilingen");
			}

			try
			{
				stmt.execute("DROP TABLE gebruikers");
			} catch (SQLException e)
			{
				System.out.println("no table gebruikers");
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

	}

}
